package org.alex.samples.dao;

import java.util.List;

import org.alex.samples.model.UserAndSubject;
import org.hibernate.type.StringType;
import org.springframework.stereotype.Repository;

@Repository
public class UserAndSubjectDAOImpl extends BaseHibernateDao<UserAndSubject> implements IUserAndSubjectDAO {

	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAllUserBySubject(Integer subjectId) {
		StringBuilder sql = new StringBuilder();
		sql.append("select user.user_id as userId ");
		sql.append(" from exam_user_subject eas ");
		sql.append(" left join exam_subject es on eas.sub_id = es.sub_id ");
		sql.append(" left join exam_user user on user.user_id = eas.user_id ");
		sql.append(" where es.sub_id = :subjectId ");
		return this.createSqlQuery(String.valueOf(sql)).addScalar("userId", StringType.INSTANCE)
				.setInteger("subjectId", subjectId).list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAllSubjectBySubject(String userId) {
		StringBuilder sql = new StringBuilder();
		sql.append("select es.sub_id as subjectId ");
		sql.append(" from exam_user_subject eas ");
		sql.append(" left join exam_subject es on eas.sub_id = es.sub_id ");
		sql.append(" left join exam_user user on user.user_id = eas.user_id ");
		sql.append(" where user.user_id = :userId ");
		return this.createSqlQuery(String.valueOf(sql)).addScalar("subjectId", StringType.INSTANCE)
				.setString("userId", userId).list();
	}

	@Override
	public void insertUserSubjects(List<String> insertList, String userId) {
		UserAndSubject userAndSubject = null;
		for(int i = 0; i < insertList.size(); i++) {
			userAndSubject = new UserAndSubject();
			userAndSubject.setSubId(Integer.valueOf(insertList.get(i)));
			userAndSubject.setUserId(userId);
			this.save(userAndSubject);
		}
	}

	@Override
	public void deleteUserSubjects(List<String> deleteList, String userId) {
		UserAndSubject userAndSubject = null;
		for(int i = 0; i < deleteList.size(); i++) {
			userAndSubject = new UserAndSubject();
			userAndSubject.setSubId(Integer.valueOf(deleteList.get(i)));
			userAndSubject.setUserId(userId);
			this.delete(userAndSubject);
		}
	}

	/*sql.append("select UserAndSubject.userId as userId ");
	sql.append(" from UserAndSubject eas ");
	sql.append(" left join ExamSubject es on eas.subId = es.id ");
	sql.append(" left join ExamUser eu on eu.id = eas.userId ");
	sql.append(" where es.id = :subjectId ");*/
}
